create database ManLab
go
use manLab

go
--Create table Doctor Detail
create table DoctorDetail(
	dID int primary key identity(1,1),
	dFName varchar(50) not null,
	dLName varchar(50) not null,
	dAddress varchar(50) not null,
	dPhone varchar(20) not null,
	dGender bit default 1 not null,
	dSpecialization varchar(50) not null,
	dPassword varchar (50)default ('123456') not null,
	dUsername varchar(50)
)
go
--Create table Patient Detail
create table PatientDetail(
	pID int primary key identity(1,1),
	pFName varchar(50) not null,
	pLName varchar(50) not null,	
	pAddress varchar(50) not null,
	pPhone varchar(20) not null,
	pGender bit default 1 not null,
	pDOB datetime not null
)
go
--Create table Staff Detail
create table StaffDetail(
	staffID int primary key identity(1,1),
	staffFName varchar(50) not null,
	staffLName varchar(50) not null,
	staffDOJ datetime not null,
	staffAddress varchar(50) not null,
	staffPhone varchar(20) not null,
	staffGender bit default 1 not null,
	staffDOB datetime not null,	
	staffEduQual varchar(50) not null,
	staffTechSkill varchar(50) not null,
	staffPassword varchar(50) default('123456') not null,
	staffUserName varchar(50)
)
go
--Create table Test
create table Test(
	testID int primary key identity(1,1),
	testName varchar(50) not null,
	testCost float default 0 not null,
	
)
go
--Create table Stock Detail
create table StockDetail(
	[sID] int primary key identity(1,1),	
	sName varchar(50) not null,
	sAvailable int not null,
	sReuseable bit default 1 not null
)
go
--Create table Item Require
create table ItemRequire(
	testID int references Test(testID) not null,
	[sID] int references StockDetail(sID) not null,
	iNoRequire int default 0 not null
)
go
--Create table Billing
create table Billing(
	bID int primary key identity(1,1),
	bDate datetime default(getdate()) not null,
	bAmount float default 0 not null,
	pID int references PatientDetail(pID) not null
)
go
--Create table Bill Detail
create table BillDetail(
	bID int references Billing(bID) not null,
	testID int references Test(testID) not null,
	testCost float default 0 not null
)
go
--Create table Report
create table Report(
    dID int references DoctorDetail(dID),
	reportID int primary key identity(1,1),
	testID int references Test(testID) not null,
	pID int references PatientDetail(pID) not null,
	staffID int references StaffDetail(staffID),
	testDate datetime default(getdate()) not null,
	report varchar(50) default('') not null,
	[status] int default -1 not null	
)
go
--Create table Admin
create table Admin(
	aPassword varchar(50) default('123456') not null
)

/*
use master
drop database ManLab
*/